﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data;
using RockCompany.Common.Lib;
using RockCompany.Master.DTO;
using RockCompany.Master.Constant;
using RockCompany.Common.Constant;
using System.Windows.Forms;

namespace RockCompany.Master.Model
{
    public class SI01FormModel : CommonModel
    {
        #region Properties
        private DataTable matsList;
        private int nSelectedRow = -1;
        #endregion

        #region Constructor
        public SI01FormModel()
        {
            DataList = new List<object>();
            matsList = new DataTable();
            dbConnector = new DBConnector();
        }
        #endregion

        #region Custom methods
        public DataTable matTypeLOV()
        {
            string sClause = "SELECT " + NGUYEN_VLIEU_VTU_DTO.N_TEN_LOAI_NVL_VTU +
                             " FROM " + NGUYEN_VLIEU_VTU_DTO.N_TABLE +
                             " GROUP BY " + NGUYEN_VLIEU_VTU_DTO.N_TEN_LOAI_NVL_VTU;
            return selectData(sClause);
        }

        public DataTable matUnitLOV()
        {
            string sClause = "SELECT " + NGUYEN_VLIEU_VTU_DTO.N_DON_VI +
                             " FROM " + NGUYEN_VLIEU_VTU_DTO.N_TABLE +
                             " GROUP BY " + NGUYEN_VLIEU_VTU_DTO.N_DON_VI;
            return selectData(sClause);
        }

        public DataRow getCheckedRow(int index)
        {
            DataRow curRow = matsList.Rows[index];
            curRow.SetField<bool>(NGUYEN_VLIEU_VTU_DTO.N_CHON, !curRow.Field<bool>(NGUYEN_VLIEU_VTU_DTO.N_CHON));
            return curRow;
        }

        public DataTable getSelectedMats()
        {
            DataTable res = matsList.Clone();
            foreach (DataRow row in DataList)
            {
                res.ImportRow(row);
            }
            return res;
        }

        public void setSelectedRow(int index) { nSelectedRow = index; }

        public DataTable searchNotMat()
        {
            string sClause = "SELECT CONVERT(BIT,'0') AS CHON,*";
            sClause += " FROM " + NGUYEN_VLIEU_VTU_DTO.N_TABLE;
            sClause += " WHERE " + NGUYEN_VLIEU_VTU_DTO.N_NVL_FLAG + " = '0'";
            matsList = selectData(sClause).Copy();
            return matsList;
        }
        #endregion

        #region Main methods
        public DataTable searchMaterial(string sMatType, string sMatNo)
        {
            string sClause = "SELECT CONVERT(BIT,'0') AS CHON,*";
            sClause += " FROM " + NGUYEN_VLIEU_VTU_DTO.N_TABLE;
            sClause += " WHERE " + NGUYEN_VLIEU_VTU_DTO.N_TEN_LOAI_NVL_VTU;
            sClause += " LIKE N'" + sMatType + "%' AND ";
            sClause += NGUYEN_VLIEU_VTU_DTO.N_MA_NVL_VTU + " LIKE N'" + sMatNo + "%'";
            matsList = selectData(sClause).Copy();
            return matsList;
        }

        public bool insertNewMat(NGUYEN_VLIEU_VTU_DTO newMat)
        {
            Dictionary<string, object> pv = new Dictionary<string, object>();
            pv.Add(NGUYEN_VLIEU_VTU_DTO.N_MA_NVL_VTU, newMat.MA_NVL_VTU);
            pv.Add(NGUYEN_VLIEU_VTU_DTO.N_TEN_LOAI_NVL_VTU, newMat.TEN_LOAI_NVL_VTU);
            pv.Add(NGUYEN_VLIEU_VTU_DTO.N_TEN_NVL_VTU, newMat.TEN_NVL_VTU);
            pv.Add(NGUYEN_VLIEU_VTU_DTO.N_DON_VI, newMat.DON_VI);
            pv.Add(NGUYEN_VLIEU_VTU_DTO.N_QUY_CACH, newMat.QUY_CACH);
            pv.Add(NGUYEN_VLIEU_VTU_DTO.N_DON_GIA, newMat.DON_GIA);
            pv.Add(NGUYEN_VLIEU_VTU_DTO.N_TY_GIA, newMat.TY_GIA);
            pv.Add(NGUYEN_VLIEU_VTU_DTO.N_NVL_FLAG, newMat.NVL_FLAG);

            pv.Add(NGUYEN_VLIEU_VTU_DTO.N_CREATION_PROGRAM_ID, CommonConstantIF.MatFormID);
            pv.Add(NGUYEN_VLIEU_VTU_DTO.N_CREATION_TIME, DateTime.Now);
            pv.Add(NGUYEN_VLIEU_VTU_DTO.N_CREATION_USER, newMat.CREATION_USER);
            return insertData(NGUYEN_VLIEU_VTU_DTO.N_TABLE, pv);
        }

        public bool removeSelectedMats()
        {
            string sWhereClause = " WHERE " + NGUYEN_VLIEU_VTU_DTO.N_MA_NVL_VTU + " IN(";
            Dictionary<string, object> pv = new Dictionary<string, object>();

            foreach (DataRow curRow in DataList)
            {
                string sP = ParaConst.P_MA_NVL_VTU + DataList.IndexOf(curRow).ToString();
                sWhereClause += sP + ",";
                pv.Add(sP, curRow[NGUYEN_VLIEU_VTU_DTO.N_MA_NVL_VTU]);
            }
            sWhereClause = sWhereClause.Substring(0, sWhereClause.Length - 1) + ")";
            DataList.Clear();
            return removeRow(NGUYEN_VLIEU_VTU_DTO.N_TABLE, sWhereClause, pv);
        }

        public bool updateMat(NGUYEN_VLIEU_VTU_DTO updateMat)
        {
            bool b = false;
            if (nSelectedRow > -1)
            {
                string sSetClause = " SET ";
                sSetClause += NGUYEN_VLIEU_VTU_DTO.N_TEN_NVL_VTU + " = " + ParaConst.P_TEN_NVL_VTU + ",";
                sSetClause += NGUYEN_VLIEU_VTU_DTO.N_QUY_CACH + " = " + ParaConst.P_QUY_CACH + ",";
                sSetClause += NGUYEN_VLIEU_VTU_DTO.N_TY_GIA + " = @" + NGUYEN_VLIEU_VTU_DTO.N_TY_GIA + ",";
                sSetClause += NGUYEN_VLIEU_VTU_DTO.N_DON_GIA + " = " + ParaConst.P_DON_GIA + ",";
                sSetClause += NGUYEN_VLIEU_VTU_DTO.N_DON_VI + " = " + ParaConst.P_DON_VI + ",";
                sSetClause += NGUYEN_VLIEU_VTU_DTO.N_TEN_LOAI_NVL_VTU + " = " + ParaConst.P_TEN_LOAI_NVL_VTU + ",";

                sSetClause += NGUYEN_VLIEU_VTU_DTO.N_LAST_UPDATE_TIME + " = " + ParaConst.P_LAST_UPDATE_TIME + ",";
                sSetClause += NGUYEN_VLIEU_VTU_DTO.N_LAST_UPDATE_PROGRAM_ID + " = " + ParaConst.P_LAST_UPDATE_PROGRAM_ID + ",";
                sSetClause += NGUYEN_VLIEU_VTU_DTO.N_LAST_UPDATE_USER + " = " + ParaConst.P_LAST_UPDATE_USER;

                string sClause = " WHERE ";
                sClause += NGUYEN_VLIEU_VTU_DTO.N_MA_NVL_VTU + " LIKE '" + updateMat.MA_NVL_VTU + "'";
                Dictionary<string, object> updateValues = new Dictionary<string, object>();
                updateValues.Add(ParaConst.P_TEN_LOAI_NVL_VTU, updateMat.TEN_LOAI_NVL_VTU);
                updateValues.Add(ParaConst.P_TEN_NVL_VTU, updateMat.TEN_NVL_VTU);
                updateValues.Add(ParaConst.P_DON_VI, updateMat.DON_VI);
                updateValues.Add(ParaConst.P_QUY_CACH, updateMat.QUY_CACH);
                updateValues.Add(ParaConst.P_DON_GIA, updateMat.DON_GIA);
                updateValues.Add("@" + NGUYEN_VLIEU_VTU_DTO.N_TY_GIA, updateMat.TY_GIA);

                updateValues.Add(ParaConst.P_LAST_UPDATE_TIME, DateTime.Now);
                updateValues.Add(ParaConst.P_LAST_UPDATE_PROGRAM_ID, "SI01");
                updateValues.Add(ParaConst.P_LAST_UPDATE_USER, updateMat.LAST_UPDATE_USER);
                b = updateData(NGUYEN_VLIEU_VTU_DTO.N_TABLE, sSetClause, updateValues, sClause); ;
            }
            return b;
        }

        public bool export2Excel(string newPath)
        {
            bool b = false;
            ExcelExporter excelExport = new ExcelExporter();
            excelExport.Trans = this.translator;
            if (excelExport.open(SI01Const.EX_NAME))
            {
                excelExport.inputValue(SI01Const.EX_POS_PRINT_DATE, DateTime.Now.ToString("dd/MM/yyyy hh:mm:ss"));

                List<DataColumn> disCol = new List<DataColumn>();
                disCol.Add(matsList.Columns[NGUYEN_VLIEU_VTU_DTO.N_MA_NVL_VTU]);
                disCol.Add(matsList.Columns[NGUYEN_VLIEU_VTU_DTO.N_TEN_NVL_VTU]);
                disCol.Add(matsList.Columns[NGUYEN_VLIEU_VTU_DTO.N_TEN_LOAI_NVL_VTU]);
                disCol.Add(matsList.Columns[NGUYEN_VLIEU_VTU_DTO.N_DON_GIA]);
                disCol.Add(matsList.Columns[NGUYEN_VLIEU_VTU_DTO.N_TY_GIA]);
                disCol.Add(matsList.Columns[NGUYEN_VLIEU_VTU_DTO.N_DON_VI]);
                disCol.Add(matsList.Columns[NGUYEN_VLIEU_VTU_DTO.N_QUY_CACH]);

                excelExport.inputRangeValues(matsList, disCol, SI01Const.EX_BEGIN_GRID_COL, SI01Const.EX_BEGIN_GRID_ROW, true);
                b = excelExport.saveExcel(newPath);
                excelExport.close();
            }
            return b;
        }
        #endregion
    }
}
